import Fluent
import FluentSQL
import Queues
import Redis
import Vapor

struct StatisticsService {
  let application: Application
  let cache: StatisticsCache
  init(application: Application, cache: StatisticsCache) {
    self.application = application
    self.cache = cache
  }
  struct StatisticsResponse: Content {
    let date: Date
    let duration: Double
    let userCount: Int?  // 仅在排行榜中使用
    let rank: Int?  // 仅在排行榜中使用
  }
  func getUserStatistics(
    userID: UUID,
    timeRange: TimeRange,
    tagID: UUID? = nil
  ) async throws -> [StatisticsResponse] {
    let (startDate, endDate) = timeRange.getDateRange()

    var statistics: [StatisticsResponse] = []
    var currentDate = startDate
    while currentDate <= endDate {
      let dayStart = Calendar.mondayFirst.startOfDay(for: currentDate)
      let nextDayStart = Calendar.mondayFirst.date(byAdding: .day, value: 1, to: dayStart)!

      // 2. 获取统计数据（优先使用缓存）
      if let cachedDuration = try await cache.getDailyStatistics(
        userID: userID,
        date: dayStart,
        tagID: tagID
      ) {
        statistics.append(
          StatisticsResponse(
            date: dayStart,
            duration: cachedDuration,
            userCount: nil,
            rank: nil
          ))
      } else {
        // 3. 数据库查询逻辑
        var query = History.query(on: application.db)
          .filter(\.$user.$id == userID)
          .filter(\.$lastListenedAt >= dayStart)
          .filter(\.$lastListenedAt < nextDayStart)
        if let tagID = tagID {
          query =
            query
            .join(Audio.self, on: \History.$audio.$id == \Audio.$id)
            .join(Album.self, on: \Audio.$album.$id == \Album.$id)
            .join(AlbumTag.self, on: \Album.$id == \AlbumTag.$album.$id)
            .filter(AlbumTag.self, \.$tag.$id == tagID)
        }
        // 4. 添加详细的查询日志
        let histories = try await query.all()
        let duration = histories.reduce(0.0) { $0 + $1.listenedDuration }
        // 5. 更新缓存
        try await cache.setDailyStatistics(
          userID: userID,
          date: dayStart,
          tagID: tagID,
          duration: duration
        )
        statistics.append(
          StatisticsResponse(
            date: dayStart,
            duration: duration,
            userCount: nil,
            rank: nil
          ))
      }
      currentDate = nextDayStart
    }
    return statistics
  }
  func getYearlyStatistics(
    userID: UUID,
    tagID: UUID? = nil
  ) async throws -> Double {
    let timeRange = TimeRange.year
    let (startDate, endDate) = timeRange.getDateRange()

    var totalDuration: Double = 0
    var currentDate = startDate
    while currentDate < endDate {
      let monthStart = Calendar.mondayFirst.startOfMonth(for: currentDate)
      if let monthlyDuration = try await cache.getMonthlyStatistics(
        userID: userID,
        date: monthStart,
        tagID: tagID
      ) {
        totalDuration += monthlyDuration
      } else {
        let nextMonth = Calendar.mondayFirst.date(byAdding: .month, value: 1, to: monthStart)!
        let monthEnd = min(nextMonth, endDate)  // 确保不超过年度结束时间
        let monthlyDuration = try await calculateMonthlyDuration(
          userID: userID,
          tagID: tagID,
          startDate: monthStart,
          endDate: monthEnd
        )
        try await cache.setMonthlyStatistics(
          userID: userID,
          date: monthStart,
          tagID: tagID,
          duration: monthlyDuration
        )
        totalDuration += monthlyDuration
      }
      currentDate = Calendar.mondayFirst.date(byAdding: .month, value: 1, to: currentDate)!
    }
    return totalDuration
  }
  private func calculateMonthlyDuration(
    userID: UUID,
    tagID: UUID?,
    startDate: Date,
    endDate: Date
  ) async throws -> Double {
    guard let sql = application.db as? SQLDatabase else {
      throw Abort(.internalServerError)
    }
    var query = SQLQueryString(
      """
      		SELECT COALESCE(SUM(h.listenedDuration), 0) as totalDuration
      		FROM \(unsafeRaw: History.schema) h
      """)

    if tagID != nil {
      query += SQLQueryString(
        """
        		JOIN \(unsafeRaw: Audio.schema) a ON h.audio = a.id
        		JOIN \(unsafeRaw: Album.schema) al ON a.album = al.id
        		JOIN \(unsafeRaw: AlbumTag.schema) at ON al.id = at.album
        """)
    }
    query += SQLQueryString(
      """
      		WHERE h.user = \(bind: userID)
      		AND h.lastListenedAt >= \(bind: startDate)
      		AND h.lastListenedAt < \(bind: endDate)
      """)
    if let tagID = tagID {
      query += SQLQueryString(" AND at.tag = \(bind: tagID)")
    }

    let rows = try await sql.raw(query).first()
    return try rows?.decode(column: "totalDuration", as: Double.self) ?? 0.0
  }
}
// MARK: - Ranking
extension StatisticsService {
  struct RankingResponse: Content {
    let rank: Int
    let userID: UUID
    let userName: String
    let userAvatar: String?
    let totalDuration: Double
    let exceedUserCount: Int
  }
  func getUserTotalDuration(
    userID: UUID,
    timeRange: TimeRange,
    tagID: UUID? = nil
  ) async throws -> Double {
    // 首先尝试从排行榜获取
    if let ranking = try await getUserRanking(
      userID: userID,
      timeRange: timeRange,
      tagID: tagID
    ) {
      return ranking.totalDuration
    }

    // 如果排行榜中没有，则直接查询数据库
    guard let sql = application.db as? SQLDatabase else {
      throw Abort(.internalServerError)
    }

    let (startDate, endDate) = timeRange.getDateRange()

    var query = SQLQueryString(
      """
      	SELECT COALESCE(SUM(h.listenedDuration), 0) as totalDuration
      	FROM \(unsafeRaw: History.schema) h
      """)

    if tagID != nil {
      query += SQLQueryString(
        """
        	JOIN \(unsafeRaw: Audio.schema) a ON h.audio = a.id
        	JOIN \(unsafeRaw: Album.schema) al ON a.album = al.id
        	JOIN \(unsafeRaw: AlbumTag.schema) at ON al.id = at.album
        """)
    }

    query += SQLQueryString(
      """
      	WHERE h.user = \(bind: userID)
      	AND h.lastListenedAt >= \(bind: startDate)
      	AND h.lastListenedAt <= \(bind: endDate)
      """)

    if let tagID = tagID {
      query += SQLQueryString(" AND at.tag = \(bind: tagID)")
    }

    let row = try await sql.raw(query).first()
    return try row?.decode(column: "totalDuration", as: Double.self) ?? 0.0
  }
  func getRankingList(
    timeRange: TimeRange,
    tagID: UUID? = nil,
    limit: Int = 100
  ) async throws -> [RankingResponse] {
    let (startDate, endDate) = timeRange.getDateRange()

    // 1. Attempt to get from cache
    if let cachedRankings = try await cache.getRanking(
      timeRange: timeRange,
      tagID: tagID
    ) {
      return Array(cachedRankings.prefix(limit))
    }
    // 2. If cache miss, use the original query logic
    guard let sql = application.db as? SQLDatabase else {
      throw Abort(.internalServerError)
    }
    var query = SQLQueryString(
      """
      		SELECT h.user, SUM(h.listenedDuration) as totalDuration
      		FROM \(unsafeRaw: History.schema) h
      """)

    if tagID != nil {
      query += SQLQueryString(
        """
        		JOIN \(unsafeRaw: Audio.schema) a ON h.audio = a.id
        		JOIN \(unsafeRaw: Album.schema) al ON a.album = al.id
        		JOIN \(unsafeRaw: AlbumTag.schema) at ON al.id = at.album
        """)
    }

    query += SQLQueryString(
      """
      		WHERE h.lastListenedAt >= \(bind: startDate)
      		AND h.lastListenedAt <= \(bind: endDate)
      """)

    if let tagID = tagID {
      query += SQLQueryString(" AND at.tag = \(bind: tagID)")
    }

    query += SQLQueryString(
      """
      		GROUP BY h.user
      		ORDER BY totalDuration DESC
      """)
    let rows = try await sql.raw(query).all()
    let rankings = try rows.map { row -> (UUID, Double) in
      let userID = try row.decode(column: "user", as: UUID.self)
      let totalDuration = try row.decode(column: "totalDuration", as: Double.self)
      return (userID, totalDuration)
    }

    let userIDs = rankings.map { $0.0 }
    let users = try await User.query(on: application.db)
      .filter(\.$id ~~ userIDs)
      .all()

    let totalUsers = try await User.query(on: application.db).count()

    let result = rankings.enumerated().prefix(limit).compactMap {
      index, ranking -> RankingResponse? in
      guard let user = users.first(where: { $0.id == ranking.0 }) else {
        return nil
      }

      return RankingResponse(
        rank: index + 1,
        userID: user.id!,
        userName: user.name,
        userAvatar: user.avatar,
        totalDuration: ranking.1,
        exceedUserCount: totalUsers - (index + 1)
      )
    }
    // 3. Update cache
    try await cache.setRanking(
      timeRange: timeRange,
      tagID: tagID,
      rankings: result
    )

    return result
  }
  func getUserRanking(
    userID: UUID,
    timeRange: TimeRange,
    tagID: UUID? = nil
  ) async throws -> RankingResponse? {

    let rankings = try await getRankingList(
      timeRange: timeRange,
      tagID: tagID,
      limit: Int.max
    )
    let userRanking = rankings.first { $0.userID == userID }

    if userRanking == nil {

    }
    return userRanking
  }
  // 新增方法: 获取用户统计排名信息(不受limit)限制
  func getUserStatisticsRanking(
    userID: UUID,
    timeRange: TimeRange,
    tagID: UUID? = nil
  ) async throws -> (rank: Int, totalUsers: Int)? {
    guard let sql = application.db as? SQLDatabase else {
      throw Abort(.internalServerError)
    }

    // 1. 构建内部查询 - 获取用户时长
    var durationQuery = SQLQueryString(
      """
      		SELECT
      				h.user,
      				SUM(h.listenedDuration) as total_duration
      		FROM \(unsafeRaw: History.schema) h
      """)

    if tagID != nil {
      durationQuery += SQLQueryString(
        """
        		JOIN \(unsafeRaw: Audio.schema) a ON h.audio = a.id
        		JOIN \(unsafeRaw: Album.schema) al ON a.album = al.id
        		JOIN \(unsafeRaw: AlbumTag.schema) at ON al.id = at.album
        """)
    }

    let (startDate, endDate) = timeRange.getDateRange()
    durationQuery += SQLQueryString(
      """
      		WHERE h.lastListenedAt >= \(bind: startDate)
      		AND h.lastListenedAt <= \(bind: endDate)
      """)

    if let tagID = tagID {
      durationQuery += SQLQueryString(" AND at.tag = \(bind: tagID)")
    }

    durationQuery += SQLQueryString(" GROUP BY h.user")

    // 2. 构建最终查询
    let query = SQLQueryString(
      """
      		SELECT
      				user_rank.position as user_position,
      				user_count.total_users
      		FROM (
      				SELECT
      						t1.user,
      						COUNT(t2.user) + 1 as position
      				FROM (\(durationQuery)) t1
      				LEFT JOIN (\(durationQuery)) t2
      						ON t2.total_duration > t1.total_duration
      				WHERE t1.user = \(bind: userID)
      				GROUP BY t1.user, t1.total_duration
      		) as user_rank
      		CROSS JOIN (
      				SELECT COUNT(*) as total_users
      				FROM \(unsafeRaw: User.schema)
      		) as user_count
      """)

    // 3. 执行查询并处理结果
    if let row = try await sql.raw(query).first() {
      let position = try row.decode(column: "user_position", as: Int.self)
      let totalUsers = try row.decode(column: "total_users", as: Int.self)
      return (position, totalUsers)
    }

    return nil
  }
  func getRankingListForCache(
    timeRange: TimeRange,
    tagID: UUID? = nil
  ) async throws -> [RankingResponse] {
    guard let sql = application.db as? SQLDatabase else {
      throw Abort(.internalServerError)
    }

    let (startDate, endDate) = timeRange.getDateRange()

    // 构建基础查询
    var query = SQLQueryString(
      """
      		SELECT h.user, SUM(h.listenedDuration) as totalDuration
      		FROM \(unsafeRaw: History.schema) h
      """)

    // 如果有标签ID，添加相关表连接
    if tagID != nil {
      query += SQLQueryString(
        """
        		JOIN \(unsafeRaw: Audio.schema) a ON h.audio = a.id
        		JOIN \(unsafeRaw: Album.schema) al ON a.album = al.id
        		JOIN \(unsafeRaw: AlbumTag.schema) at ON al.id = at.album
        """)
    }

    // 添加时间范围条件
    query += SQLQueryString(
      """
      		WHERE h.lastListenedAt >= \(bind: startDate)
      		AND h.lastListenedAt < \(bind: endDate)
      """)

    // 如果有标签ID，添加标签过滤
    if let tagID = tagID {
      query += SQLQueryString(" AND at.tag = \(bind: tagID)")
    }

    // 分组和排序
    query += SQLQueryString(
      """
      		GROUP BY h.user
      		HAVING SUM(h.listenedDuration) > 0
      		ORDER BY totalDuration DESC
      """)

    // 执行查询
    let rows = try await sql.raw(query).all()

    // 获取用户ID列表
    let rankings = try rows.map { row -> (UUID, Double) in
      let userID = try row.decode(column: "user", as: UUID.self)
      let totalDuration = try row.decode(column: "totalDuration", as: Double.self)
      return (userID, totalDuration)
    }

    // 获取用户信息
    let userIDs = rankings.map { $0.0 }
    let users = try await User.query(on: application.db)
      .filter(\.$id ~~ userIDs)
      .all()

    // 获取总用户数
    let totalUsers = try await User.query(on: application.db).count()

    // 构建完整的排行榜响应
    let result = rankings.enumerated().compactMap { index, ranking -> RankingResponse? in
      guard let user = users.first(where: { $0.id == ranking.0 }) else {
        return nil
      }

      return RankingResponse(
        rank: index + 1,
        userID: user.id!,
        userName: user.name,
        userAvatar: user.avatar,
        totalDuration: ranking.1,
        exceedUserCount: totalUsers - (index + 1)
      )
    }

    return result
  }
}
// MARK: - Scheduler
struct StatisticsScheduler {
  let application: Application
  let cache: StatisticsCache
  func updatePreviousDayStatistics() async throws {
    let now = Date()
    let calendar = Calendar.mondayFirst
    // 更优雅地获取昨天的开始时间
    let todayStart = calendar.startOfDay(for: now)
    let yesterdayStart = calendar.date(byAdding: .day, value: -1, to: todayStart)!
    let timeRange = TimeRange.custom(start: yesterdayStart, end: yesterdayStart)
    let (startDate, endDate) = timeRange.getDateRange()

    guard let sql = application.db as? SQLDatabase else {
      throw Abort(.internalServerError)
    }

    // 1. 更新总体统计
    let query = SQLQueryString(
      """
      				SELECT h.user, SUM(h.listenedDuration) as totalDuration
      				FROM \(unsafeRaw: History.schema) h
      				WHERE h.lastListenedAt >= \(bind: startDate)
      				AND h.lastListenedAt < \(bind: endDate)
      				GROUP BY h.user
      """)

    let rows = try await sql.raw(query).all()
    for row in rows {
      let userID = try row.decode(column: "user", as: UUID.self)
      let duration = try row.decode(column: "totalDuration", as: Double.self)

      try await cache.setDailyStatistics(
        userID: userID,
        date: startDate,
        tagID: nil,
        duration: duration
      )
    }

    // 2. 更新带标签的统计
    let tags = try await Tag.query(on: application.db).all()
    for tag in tags {
      let tagQuery = SQLQueryString(
        """
        				SELECT h.user, SUM(h.listenedDuration) as totalDuration
        				FROM \(unsafeRaw: History.schema) h
        				JOIN \(unsafeRaw: Audio.schema) a ON h.audio = a.id
        				JOIN \(unsafeRaw: Album.schema) al ON a.album = al.id
        				JOIN \(unsafeRaw: AlbumTag.schema) at ON al.id = at.album
        				WHERE h.lastListenedAt >= \(bind: startDate)
        				AND h.lastListenedAt < \(bind: endDate)
        				AND at.tag = \(bind: tag.id!)
        				GROUP BY h.user
        """)

      let tagRows = try await sql.raw(tagQuery).all()
      for row in tagRows {
        let userID = try row.decode(column: "user", as: UUID.self)
        let duration = try row.decode(column: "totalDuration", as: Double.self)

        try await cache.setDailyStatistics(
          userID: userID,
          date: startDate,
          tagID: tag.id!,
          duration: duration
        )
      }
    }
  }

  func updateTodayStatistics() async throws {
    let timeRange = TimeRange.day
    let (startDate, endDate) = timeRange.getDateRange()

    guard let sql = application.db as? SQLDatabase else {
      throw Abort(.internalServerError)
    }

    // 1. 更新总体统计
    let query = SQLQueryString(
      """
      				SELECT h.user, SUM(h.listenedDuration) as totalDuration
      				FROM \(unsafeRaw: History.schema) h
      				WHERE h.lastListenedAt >= \(bind: startDate)
      				AND h.lastListenedAt < \(bind: endDate)
      				GROUP BY h.user
      """)

    let rows = try await sql.raw(query).all()
    for row in rows {
      let userID = try row.decode(column: "user", as: UUID.self)
      let duration = try row.decode(column: "totalDuration", as: Double.self)

      try await cache.setDailyStatistics(
        userID: userID,
        date: startDate,
        tagID: nil,
        duration: duration
      )
    }

    // 2. 更新带标签的统计
    let tags = try await Tag.query(on: application.db).all()
    for tag in tags {
      let tagQuery = SQLQueryString(
        """
        				SELECT h.user, SUM(h.listenedDuration) as totalDuration
        				FROM \(unsafeRaw: History.schema) h
        				JOIN \(unsafeRaw: Audio.schema) a ON h.audio = a.id
        				JOIN \(unsafeRaw: Album.schema) al ON a.album = al.id
        				JOIN \(unsafeRaw: AlbumTag.schema) at ON al.id = at.album
        				WHERE h.lastListenedAt >= \(bind: startDate)
        				AND h.lastListenedAt < \(bind: endDate)
        				AND at.tag = \(bind: tag.id!)
        				GROUP BY h.user
        """)

      let tagRows = try await sql.raw(tagQuery).all()
      for row in tagRows {
        let userID = try row.decode(column: "user", as: UUID.self)
        let duration = try row.decode(column: "totalDuration", as: Double.self)

        try await cache.setDailyStatistics(
          userID: userID,
          date: startDate,
          tagID: tag.id!,
          duration: duration
        )
      }
    }
  }

  func updateRankingCache() async throws {

    let timeRanges: [TimeRange] = [.day, .week, .month, .year]
    let tags = try await Tag.query(on: application.db).all()

    // 更新无标签的排行榜
    for timeRange in timeRanges {

      // 使用新方法获取数据
      let rankings = try await application.statistics.getRankingListForCache(
        timeRange: timeRange,
        tagID: nil
      )

      // 更新缓存
      try await cache.setRanking(
        timeRange: timeRange,
        tagID: nil,
        rankings: rankings
      )

    }

    // 更新带标签的排行榜
    for tag in tags {

      for timeRange in timeRanges {
        let rankings = try await application.statistics.getRankingListForCache(
          timeRange: timeRange,
          tagID: tag.id
        )

        try await cache.setRanking(
          timeRange: timeRange,
          tagID: tag.id,
          rankings: rankings
        )

      }
    }

  }
  func updateMonthlyStatistics() async throws {
    let now = Date()
    // 使用日历方式获取当月开始时间
    let calendar = Calendar.mondayFirst
    let todayComponents = calendar.dateComponents([.year, .month], from: now)
    let monthStart = calendar.date(from: todayComponents)!

    // 获取下月开始时间（作为结束时间）
    let nextMonthComponents = DateComponents(
      year: todayComponents.year, month: todayComponents.month! + 1, day: 1)
    let nextMonthStart = calendar.date(from: nextMonthComponents)!

    guard let sql = application.db as? SQLDatabase else {
      throw Abort(.internalServerError)
    }

    // 1. 更新总体月度统计
    let query = SQLQueryString(
      """
      		SELECT h.user, SUM(h.listenedDuration) as totalDuration
      		FROM \(unsafeRaw: History.schema) h
      		WHERE h.lastListenedAt >= \(bind: monthStart)
      		AND h.lastListenedAt < \(bind: nextMonthStart)
      		GROUP BY h.user
      """)

    let rows = try await sql.raw(query).all()
    for row in rows {
      let userID = try row.decode(column: "user", as: UUID.self)
      let duration = try row.decode(column: "totalDuration", as: Double.self)

      try await cache.setMonthlyStatistics(
        userID: userID,
        date: monthStart,  // 使用月初时间作为缓存键
        tagID: nil,
        duration: duration
      )
    }

    // 2. 更新带标签的月度统计
    let tags = try await Tag.query(on: application.db)
      .group(.or) { group in
        group.filter(\.$isDeleted == false)
        group.filter(\.$isDeleted == nil)
      }
      .all()
    for tag in tags {
      let tagQuery = SQLQueryString(
        """
        		SELECT h.user, SUM(h.listenedDuration) as totalDuration
        		FROM \(unsafeRaw: History.schema) h
        		JOIN \(unsafeRaw: Audio.schema) a ON h.audio = a.id
        		JOIN \(unsafeRaw: Album.schema) al ON a.album = al.id
        		JOIN \(unsafeRaw: AlbumTag.schema) at ON al.id = at.album
        		WHERE h.lastListenedAt >= \(bind: monthStart)
        		AND h.lastListenedAt < \(bind: nextMonthStart)
        		AND at.tag = \(bind: tag.id!)
        		GROUP BY h.user
        """)

      let tagRows = try await sql.raw(tagQuery).all()
      for row in tagRows {
        let userID = try row.decode(column: "user", as: UUID.self)
        let duration = try row.decode(column: "totalDuration", as: Double.self)

        try await cache.setMonthlyStatistics(
          userID: userID,
          date: monthStart,  // 使用月初时间作为缓存键
          tagID: tag.id!,
          duration: duration
        )
      }
    }

  }
}
extension StatisticsService {
  static func handleDatabaseEvent(
    app: Application,
    history: History
  ) async throws {
    let sql = app.db as! SQLDatabase
    guard let lastListenedAt = history.lastListenedAt else {
      return
    }
    let eventDayStart = Calendar.mondayFirst.startOfDay(for: lastListenedAt)
    let userID = history.$user.id

    // 1. 获取相关的标签ID
    let query = SQLQueryString(
      """
      				SELECT DISTINCT at.tag
      				FROM \(unsafeRaw: Audio.schema) a
      				JOIN \(unsafeRaw: Album.schema) al ON a.album = al.id
      				JOIN \(unsafeRaw: AlbumTag.schema) at ON al.id = at.album
      				WHERE a.id = \(bind: history.$audio.id)
      """)

    let rows = try await sql.raw(query).all()
    let tagIDs = try rows.map { row in
      try row.decode(column: "tag", as: UUID.self)
    }

    // 2. 使用 StatisticsCache 来失效缓存
    // 2.1 删除无标签的统计缓存
    try await app.statistics.cache.invalidateStatistics(
      userID: userID,
      date: eventDayStart,
      tagID: nil
    )

    // 2.2 删除带标签的统计缓存
    for tagID in tagIDs {
      try await app.statistics.cache.invalidateStatistics(
        userID: userID,
        date: eventDayStart,
        tagID: tagID
      )
    }

    // 2.3 删除最近7天音频数量的缓存
    try await app.statistics.cache.delete(
      forKey: CacheConfig.KeyBuilder.recentAudiosCount(userID: userID),
      on: app.redis
    )

    // 2.4 删除全局音频数量分布缓存
    try await app.statistics.cache.delete(
      forKey: CacheConfig.KeyBuilder.allUsersRecentAudiosDistribution(),
      on: app.redis
    )

    // 2.5 删除标签分布缓存
    try await app.statistics.cache.delete(
      forKey: CacheConfig.KeyBuilder.tagsDistribution(userID: userID),
      on: app.redis
    )
  }
}
extension StatisticsService {
  struct RecentAudiosResult {
    let count: Int
    let exceedPercentage: Double
  }

  func getUserRecentAudiosStats(userID: UUID) async throws -> RecentAudiosResult {
    // 计算最近7天的时间范围
    let calendar = Calendar.mondayFirst
    let now = Date()
    let startDate = calendar.date(byAdding: .day, value: -6, to: calendar.startOfDay(for: now))!
    let endDate = calendar.date(byAdding: .day, value: 1, to: calendar.startOfDay(for: now))!

    // 先尝试从缓存获取用户的音频数量
    var userAudioCount: Int
    if let cachedCount = try await cache.getRecentAudiosCount(userID: userID) {
      userAudioCount = cachedCount
    } else {
      // 如果缓存未命中，查询数据库
      guard let sql = application.db as? SQLDatabase else {
        throw Abort(.internalServerError)
      }

      // 查询指定用户最近7天听过的不同音频数量
      let userQuery = SQLQueryString(
        """
        	SELECT COUNT(DISTINCT h.audio) as audioCount
        	FROM \(unsafeRaw: History.schema) h
        	WHERE h.user = \(bind: userID)
        	AND h.lastListenedAt >= \(bind: startDate)
        	AND h.lastListenedAt < \(bind: endDate)
        	AND h.listenedDuration > 0
        """)

      let userRow = try await sql.raw(userQuery).first()
      userAudioCount = try userRow?.decode(column: "audioCount", as: Int.self) ?? 0

      // 更新缓存
      try await cache.setRecentAudiosCount(userID: userID, count: userAudioCount)
    }

    // 获取所有用户的音频数量分布
    var allUsersCounts: [Int]
    if let cachedDistribution = try await cache.getAllUsersRecentAudiosDistribution() {
      allUsersCounts = cachedDistribution
    } else {
      // 如果缓存未命中，查询数据库
      guard let sql = application.db as? SQLDatabase else {
        throw Abort(.internalServerError)
      }

      // 查询所有用户最近7天的不同音频数量统计
      let allUsersQuery = SQLQueryString(
        """
        	SELECT h.user, COUNT(DISTINCT h.audio) as audioCount
        	FROM \(unsafeRaw: History.schema) h
        	WHERE h.lastListenedAt >= \(bind: startDate)
        	AND h.lastListenedAt < \(bind: endDate)
        	AND h.listenedDuration > 0
        	GROUP BY h.user
        """)

      let allUsersRows = try await sql.raw(allUsersQuery).all()
      allUsersCounts = try allUsersRows.map { row in
        try row.decode(column: "audioCount", as: Int.self)
      }

      // 更新缓存
      try await cache.setAllUsersRecentAudiosDistribution(distribution: allUsersCounts)
    }

    // 计算百分比
    var exceedPercentage: Double = 0
    if !allUsersCounts.isEmpty {
      let lowerUsers = allUsersCounts.filter { $0 < userAudioCount }
      exceedPercentage = Double(lowerUsers.count) / Double(allUsersCounts.count) * 100
    }

    return RecentAudiosResult(
      count: userAudioCount,
      exceedPercentage: exceedPercentage
    )
  }
}

// MARK: - Tags Distribution
extension StatisticsService {
  func getTagsDistribution(userID: UUID) async throws -> TagDistributionResponse {
    // 1. 先尝试从缓存获取
    if let cachedDistribution = try await cache.getTagsDistribution(userID: userID) {
      return cachedDistribution
    }

    // 2. 计算最近7天的时间范围
    let calendar = Calendar.mondayFirst
    let now = Date()
    let startDate = calendar.date(byAdding: .day, value: -6, to: calendar.startOfDay(for: now))!
    let endDate = calendar.date(byAdding: .day, value: 1, to: calendar.startOfDay(for: now))!

    guard let sql = application.db as? SQLDatabase else {
      throw Abort(.internalServerError)
    }

    // 3. 获取用户最近7天的总收听时长
    let totalQuery = SQLQueryString(
      """
      	SELECT COALESCE(SUM(h.listenedDuration), 0) as totalDuration
      	FROM \(unsafeRaw: History.schema) h
      	WHERE h.user = \(bind: userID)
      	AND h.lastListenedAt >= \(bind: startDate)
      	AND h.lastListenedAt < \(bind: endDate)
      """)

    let totalRow = try await sql.raw(totalQuery).first()
    let totalDuration = try totalRow?.decode(column: "totalDuration", as: Double.self) ?? 0.0

    // 4. 获取所有标签
    let tags = try await Tag.query(on: application.db)
      .group(.or) { group in
        group.filter(\.$isDeleted == false)
        group.filter(\.$isDeleted == nil)
      }
      .all()

    // 5. 获取每个标签的收听时长
    var tagDistributions: [TagDistributionResponse.TagDistribution] = []

    for tag in tags {
      guard let tagID = tag.id else { continue }

      let tagQuery = SQLQueryString(
        """
        	SELECT COALESCE(SUM(h.listenedDuration), 0) as tagDuration
        	FROM \(unsafeRaw: History.schema) h
        	JOIN \(unsafeRaw: Audio.schema) a ON h.audio = a.id
        	JOIN \(unsafeRaw: Album.schema) al ON a.album = al.id
        	JOIN \(unsafeRaw: AlbumTag.schema) at ON al.id = at.album
        	WHERE h.user = \(bind: userID)
        	AND h.lastListenedAt >= \(bind: startDate)
        	AND h.lastListenedAt < \(bind: endDate)
        	AND at.tag = \(bind: tagID)
        """)

      let tagRow = try await sql.raw(tagQuery).first()
      let tagDuration = try tagRow?.decode(column: "tagDuration", as: Double.self) ?? 0.0

      // 计算百分比，避免除零错误
      let percentage = totalDuration > 0 ? (tagDuration / totalDuration) * 100 : 0

      tagDistributions.append(
        TagDistributionResponse.TagDistribution(
          id: tagID,
          name: tag.name,
          duration: tagDuration,
          percentage: percentage
        )
      )
    }

    // 6. 按时长排序
    tagDistributions.sort { $0.duration > $1.duration }

    // 7. 构造结果
    let result = TagDistributionResponse(
      totalDuration: totalDuration,
      tags: tagDistributions
    )

    // 8. 更新缓存
    try await cache.setTagsDistribution(userID: userID, distribution: result)

    return result
  }
}
